import functools
from functools import wraps
import redis
import jwt
import xlsxwriter as xlsxwriter
from flask import Blueprint, request, make_response
import os
from flask import Blueprint
from xlrd import open_workbook

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
from db import Dd
import datetime
import mimetypes
from jwttoken import Myjwt

views = Blueprint('views', __name__)


# 装饰器
def timer(func):
    @functools.wraps(func)
    def decor(*args, **kwargs):
        token = request.args.get('token')
        username = request.args.get('username')
        id = request.args.get('id')
        print(token)
        if token:
            jwts = Myjwt()
            data = jwts.decode_jwt(token)
            print(data)
            print(data['username'])
            if data['username'] == username and data['id'] == id:
                return {'code': '403', 'msg': 'token权限验证失败,请重新登录'}
        return func(*args, **kwargs)

    return decor


# 导入excel
@views.route('/upload', methods=['POST'])
def uploads():
    print(request.files)
    fileilename = request.files['file']
    print(fileilename)
    # print(request.files['file'])/
    fileilename.save(os.path.join('./static/' + fileilename.filename))
    # name = 'static/' + fileilename.filename
    # bk = open_workbook(name, encoding_override='utf-8')
    # sh = bk.sheets()[0]
    # nrows = sh.nrows
    # ncols = sh.ncols
    # # 行
    # for i in range(2, nrows):
    #     row_date = sh.row_values(i)
    #     for x in range(0, ncols):
    #         print(row_date[x])
    return {'code': '200', 'filename': fileilename.filename}


# 导出exls
@views.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


# 获取无限极
@views.route('/acporgn', methods=["GET"])
@timer
def acporgn():
    sql = "select * from `organ`"
    db = Dd()
    orgn = db.get_all(sql)
    if not orgn:
        return orgn
    #  先把列表重组成字典
    dict1 = {}
    list1 = []
    for i in orgn:
        i['label'] = i['name']
        dict1[i['id']] = i
    for j in orgn:
        level = j['level']
        j['label'] = j['name']
        #  pid等于0，就是顶级，
        if level == 0:
            # 直接加入列表
            list1.append(j)
        else:
            #  查看父类是否有子类
            if 'children' not in dict1[level]:
                dict1[level]['children'] = []
            dict1[level]['children'].append(j)
    return {'code': '200', 'list1': list1}


# #  无限极分类数据重构展示

# 添加数据
@views.route('/reliable_orgn', methods=['POST'])
def reliable_orgn():
    name = request.form.get('name')
    codtype = request.form.get('codtype')
    sort = request.form.get('sort')
    type = request.form.get('type')
    id = request.form.get('id')
    print(id)
    if type == 1:
        level = 0
        db = Dd()
        orgn = db.get_all("select * from `organ` where level=0")
        orgn = len(orgn)
        coding = 'AO' + str(orgn + 1)
        print(coding)
        sql = "insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (
            name, coding, sort, level, codtype)
        db.insert(sql)
        return {'code': '200', 'msg': '添加成功'}
    else:
        level = id
        db = Dd()
        data = db.get_one("select * from `organ` where id=%s" % id)
        orgn = db.get_all("select * from `organ` where level=%s" % id)
        orgn = len(orgn)
        print(data)
        coding = data['coding'] + 'AO' + str(orgn + 1)
        sql = "insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (
            name, coding, sort, level, codtype)
        db.insert(sql)
        return {'code': '200', 'msg': '添加成功'}


# 获取详情
@views.route('/reliable_orgn', methods=['GET'])
def reliable_or():
    id = request.args.get('id')
    db = Dd()
    orgn = db.get_one("select * from `organ` where level=%s" % id)
    names = orgn['name']
    return {'code': '200', 'names': names}


from se import Myes
import json


@views.route('/esget')
def esget():
    es = Myes('testtabel', 'test-type')
    body = {
        # match 是单个
        #    "match":{
        #     "name":"abc"
        # }
        # multi_match是双个类型来筛选
        "multi_match": {
            "query": "abc",
            "fields": ["name", "content"]
        }
    }
    data = es.search(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'


@views.route('/estestinsert')
def estest():
    es = Myes('testtabel', 'test-type')
    try:
        db = Dd()
        sql = "select * from test limit 0,10"
        data = db.get_all(sql)
        j = -1
        for i in data:
            j += 1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'


@views.route('/get_userguan', methods=['GET'])
def get_userguan():
    page = request.args.get('page')
    if page:
        print(page)
        size = 10
        db = Dd()
        page = (int(page) - 1) * size
        sql = "select * from user limit %s,10" % page
        list = db.get_all(sql)
        return {'code': '200', 'list': list}
    else:
        rbac = RBAC()
        list = rbac.get_all_role()
        return {'code': '200', 'list': list}


@views.route('/post_user', methods=['POST'])
def post_user():
    username = request.form.get('username')
    password = request.form.get('password')
    passwords = request.form.get('passwords')
    phone = request.form.get('phone')
    email = request.form.get('email')
    jobnum = request.form.get('jobnum')
    geter = request.form.get('geter')
    roleid = request.form.get('roleid')
    name = request.form.get('name')
    print(username)
    if password != passwords:
        return {'code': '403', 'msg': '确认密码错误'}
    db = Dd()
    sql = "insert into  `user` (username,password,name,phone,email,jobnum,geter,roleid) values ('%s','%s','%s',%s,'%s',%s,%s,%s)" % (
        username, password, name, phone, email, jobnum, geter, roleid)
    db.insert(sql)
    return {'code': '200', 'msg': '添加成功'}


@views.route('/delete_user', methods=['DELETE'])
def delete():
    pid = request.args.get('pid')
    print(pid)
    db = Dd()
    sql = "delete from `user` where id=%s" % pid
    db.delete(sql)
    return {'code': '200', 'msg': '删除成功'}


from tool import ACL, RBAC, RedisCon, Queue


# 获取权限
@views.route('/get_roleqan', methods=['GET'])
def get_roleqan():
    id = request.args.get('id')
    acl = RBAC()
    list = acl.get_one(id)
    print(list)
    return {'code': '200', 'list': list}


@views.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')
    sql = "select * from `user` where username='%s'" % (username)
    db = Dd()
    r = redis.Redis()
    data = db.get_one(sql)
    # if data['user_type'] == 2:
    #     num = r.ttl('dongjie%s' % data['id'])
    #     return {'code': '403', 'msg': '你已冻结,还有%s秒解冻,请稍等' % num}
    if data['username'] != username and data['password'] != str(password):
        return {'code': 403, 'msg': '请重新登录'}
    myjwt = Myjwt()
    token = myjwt.encode_jwt({'username': data['username'], 'id': data['id']})
    return {'code': '200', 'userid': data['id'], 'token': token, 'msg': '登录成功', 'username': data['username'],
            'name': data['name']}


# 修改成冻结状态
@views.route('/update_one', methods=['PUT'])
def update_one():
    id = request.form.get('id')
    r = redis.Redis()
    db = Dd()
    db.update("update  `user` set user_type=2 where id=%s " % id)
    r.setex('dongjie%s' % id, 120, id)
    return {'code': 200, 'msg': '冻结成功'}


# 查看角色权限
@views.route('/get_node', methods=['GET'])
def get_node():
    id = request.args.get('id')
    db = Dd()
    list = db.get_all(
        "select node.name,node.id from `role` inner join `node_role` on role.id=node_role.roleid inner join node on node.id=node_role.node_id where role.id=%s" % id)
    data = []
    sql = 'select * from node where '
    sql1 = ''
    for j in list:
        sql1 += 'node.id != %s and ' % j['id']
    data_sql = sql + sql1

    data_sql = data_sql.strip('and ')
    # 定义一级列表
    lists = []
    # 获取有的数据
    try:
        # 这是没有的数据
        list1 = db.get_all(data_sql)
        print(list1)
    except Exception as e:
        list1 = db.get_all("select * from node")
    for i in list1:
        if i['auid'] == 0:
            lists.append({'value': i['id'], 'desc': i['name']})
        else:
            # 这是角色没有的数据
            data.append({'value': i['id'], 'desc': i['name']})
    data1 = []
    for j in list:
        # 这是已有的数据
        data1.append({'key': j['id'], 'label': j['name']})
    return {'code': '200', 'data': data, 'list': data1, 'lists': lists}


# 授权角色权限
@views.route('/post_liulan', methods=['POST', 'GET'])
def post_liulan():
    db = Dd()
    if request.method == 'POST':
        pid = request.form.get('pid')
        print(pid)
        result = request.form.get('result')
        print(result)
        result = eval(result)
        rbac = RBAC()
        for i in result:
            rbac.Inheritance(pid, i)
        return 'ok'

    # 获取所有参数详情
    if request.method == 'GET':
        res = db.get_all("select * from workflak")
        return {'code': '200', 'list': res}


# 模糊查询
@views.route('/sho_msg', methods=['GET'])
def show_msg():
    username = request.args.get('username')
    name = request.args.get('name')
    phone = request.args.get('phone')
    db = Dd()
    if username:
        data = db.get_one("select * from `user` where username like '%s'" % username)
        print(data)
    if name:
        data = db.get_one("select * from `user` where name like '%s'" % name)
    if phone:
        data = db.get_one("select * from `user` where phone like %s" % phone)
    if username and name:
        data = db.get_one("select * from `user` where username like '%s' and name like '%s'" % username, name)
    if username and phone:
        data = db.get_one("select * from `user` where username like '%s' and name like %s" % username, phone)
    return {'code': '200', 'data': data}


def timer(func):
    @functools.wraps(func)
    def decor(*args, **kwargs):
        token = request.args.get('token')
        myjwt = Myjwt()
        num = myjwt.decode_jwt(token)
        print(num['id'])
        r = ABAC()
        data = r.get_all('abac')
        ip = request.remote_addr
        times = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
        db = Dd()
        for (key, value) in data.items():
            if key == 'ip':
                if str(value) != str(ip):
                    return 'ip限制'
            if key == 'time':
                starttime = eval(value)[0]
                end = eval(value)[1]
                if starttime > times and times > end:
                    return '时间限制'
            if key == 'role':
                roleid = db.get_one("select roleid from user where id=%s" % num['id'])
                if roleid['roleid'] != int(value):
                    return '角色限制'
            if request.method != 'GET':
                return '请求方法限制'
        return func(*args, **kwargs)

    return decor


from tool import ABAC
import time


@views.route('/hash_shu', methods=['GET'])
@timer
def hash_shu():
    r = ABAC()
    r.set_insert('abac', 'ip', '127.0.0.1')
    r.set_insert('abac', 'time', "['2020-10-15','2022-1-30']")
    r.set_insert('abac', 'role', '1')
    data = r.get_all('abac')
    times = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
    for (key, value) in data.items():
        if key == 'time':
            print(eval(value)[0])
    return 'ok'


@views.route('/get_shujuyuan', methods=['GET', 'PUT'])
def get_shujuyuan():
    if request.method == 'GET':
        r = RedisCon()
        res = r.str_get('get_type')
        db = Dd()
        list = []
        if res:
            list = json.loads(res)
            print(list)
        else:
            data = db.get_all("select * from data_source")
            for i in data:
                dict = {'id': i['id'], 'name': i['name']}
                print("select id,name,type from '%s'" % i['tablename'])
                res = db.get_all("select id,name,type from `%s`" % (str(i['tablename'])))
                print(515)
                dict['mes'] = res
                list.append(dict)

            r.str_set('get_type', json.dumps(list))
        return {'code': '200', 'list': list}
    if request.method == 'PUT':
        id = request.form.get('id')
        print(id)
        prams = request.form.get('dict')
        print(prams)
        db = Dd()
        print("update workflak set params='%s' where id=%s" % (prams, id))
        db.update("update workflak set params='%s' where id=%s" % (prams, int(id)))
        return {'code': '200', 'msg': '添加成功'}


@views.route('/get_worf', methods=['GET'])
def get_worf():
    id = request.args.get('id')
    db = Dd()
    list = []
    res = db.get_all('select * from workflak where id=%s' % id)
    print(res)
    for i in res:
        list = i['params']
    print(list)
    return {'code': '200', 'list': res, 'dict': list}


# 提交任务表
@views.route('/post_task', methods=['POST', 'GET'])
def post_task():
    if request.method == 'POST':
        named = request.form
        print(named)
        list = []
        for (j, value) in named.items():
            if j[:-3] == "node_id":
                list.append(value)
        name = request.form.get('values[姓名]')
        state_time = request.form.get('values[开始时间]')
        uploads_name = request.form.get('values[文件]')
        end_time = request.form.get('values[结束时间]')
        sta_type = request.form.get('values[请假理由]')
        sta_value = request.form.get('values[请假类型]')
        node_id = request.form.get('node_id[0]')
        userid = request.form.get('values[userid]')

        # print(node_id)
        title = name + sta_value
        # print(title)
        params = [
            {'姓名': name, '请假类型': sta_value, '开始时间': state_time, '文件': uploads_name, '结束时间': end_time, '请假理由': sta_type}]
        db = Dd()
        # lists=json.dumps(list)
        print('insert into `task` (title,params,reviewer,fist,userid) values ("%s","%s","%s",%s,%s)' % (
            title, params, list, node_id,userid))
        db.insert('insert into `task` (title,params,reviewer,fist,userid) values ("%s","%s","%s",%s,%s)' % (
            title, params, list, node_id,userid))
        queue = Queue()
        sql_two = db.get_one("select * from task where title='%s'" % title)
        for o in list:
            sql_one = db.get_one("select id,name from user where id=%s" % o)
            queue.set_insert('tasks' + str(sql_two['id']), json.dumps(sql_one))
        # 添加hash
        ab = ABAC()
        ab.set_insert('task' + node_id, sql_two['id'], sql_two['title'])
        return {'code': '200'}
    if request.method == 'GET':
        db = Dd()
        res = db.get_all("select  * from organ")
        dict = {}
        list = []
        for i in res:
            dict[i['id']] = i
        for j in res:
            level = j['level']
            j['label'] = j['name']
            j['type'] = 1
            if not level:
                list.append(j)
            else:
                if 'children' not in dict[level]:
                    dict[level]['children'] = []
                # 查询此部门下所有员工
                sql = "select * from user where organid=%d" % (j['id'])
                res = db.get_all(sql)
                # print(res)
                for i in res:
                    i['label'] = i['name']
                    i['type'] = 3
                userlist = []
                if res and res != ():
                    userlist = res
                j['type'] = 2
                j['children'] = userlist
                dict[level]['children'].append(j)
        return {'code': '200', 'res': list}


@views.route('/my_task', methods=['GET'])
def my_task():
    id = request.args.get('id')
    ab = ABAC()
    res = ab.get_all('task' + str(id))
    dict = []
    count = 0
    for (i, value) in res.items():
        count += 1
        dict.append({'id': i, 'name': value})
    return {'code': 200, 'dict': dict, 'count': count}


@views.route('/get_my_task', methods=['GET'])
def get_my_task():
    id = request.args.get('id')
    userid = request.args.get('userid')
    if id:
        r = Queue()
        list = r.get_all_queue('tasks' + str(id))
        dict = []
        for i in list:
            dict.append(json.loads(i))
        return {'code': 200, 'list': dict}
    if  userid:
        db=Dd()
        data=db.get_all("select * from task where userid=%s" % userid)
        for i  in  data:
            list = []
            prams=i['params'].replace("[", '').replace("]", '').replace("\"", '')
            print(json.dumps(prams))
            if  list ==[]:
                list.append(eval(prams))
            print(list)
            # print(lists)
            i['dilist']=list
            i['reviewer']=eval(i['reviewer'])
        print(data)
        return {'code':200,'list':data}


import ast


# 获取任务详情
@views.route('/my_task_one', methods=['GET', 'POST'])
def my_task_one():
    if request.method == 'GET':
        id = request.args.get('id')
        db = Dd()
        dict = db.get_one("select * from task where id =%s" % id)
        param = dict['params']
        param = param.replace("[", '').replace("]", '').replace("\"", '')
        list = []
        list.append(eval(param))
        dise = dict['reviewer']
        dict['reviewer'] = eval(dise)
        # 获取审核任务
        my_audi_task=db.get_all("select  * from audit_task where taskid=%s" % id)
        print(my_audi_task)
        return {'code': 200, 'list': dict, 'param': list,'my_audi_task':my_audi_task}
    if request.method == 'POST':
        taskid = request.form.get('taskid')
        userid = request.form.get('userid')
        username = request.form.get('username')
        is_agree = request.form.get('is_agree')
        reason = request.form.get('reason')
        db = Dd()
        db.insert(
            "insert into audit_task (taskid,userid,username,is_agree,reason,add_time)  values (%s,%s,'%s',%s,'%s','%s')" % (
            taskid, userid, username, is_agree, reason, datetime.datetime.now()))
        dict = db.get_one("select * from task where id=%d" % (int(taskid)))
        key = 'task%d' % (int(userid))
        ab = ABAC()
        ab.del_one(key, int(taskid))
        if int(is_agree) == 1:
            # 同意，查询任务，更新下一审批人，redis清除当前审批人，加入下一审批人，
            # 任务表中更新任务状态，如果是最后一个审批人，查新成已经完成，其它审核中
            dise = dict['reviewer']
            dise = eval(dise)
            # 是不是最后一个审批人
            index = dise.index(userid)
            if index == len(dise) - 1:
                sql1 = "update task set state=3,fist=0 where id=%s" % (int(taskid))
                db.update(sql1)
            else:
                sql1 = "update task set state=2,fist=%s where id=%s" % (dise[index + 1], int(taskid))
                db.update(sql1)
                # 添加redis
                newkey = 'task%d' % (int(dise[index + 1]))
                # 把下一审批人加入到redis
                ab.set_insert(newkey, int(taskid), dict['title'])
        else:
            dise = dict['reviewer']
            dise = eval(dise)
            # 是不是最后一个审批人
            index = dise.index(userid)
            if index == 0:
                # 不同意，把redis中当前审批人清除，任务表中的下一审批人清空
                sql1 = "update task set state=4,fist=0 where id=%s" % (int(taskid))
                db.update(sql1)
            if index != 0:
                sql1 = "update task set state=2,fist=%s where id=%s" % (dise[index - 1], int(taskid))
                db.update(sql1)
                # 添加redis
                newkey = 'task%d' % (int(dise[index - 1]))
                # 把下一审批人加入到redis
                ab.set_insert(newkey, int(taskid), dict['title'])
        return {"code": 200}
